<?php
class Sql {
	public  $query;
	public  $cache;
	public  $table;
	public $sql;
	public static $showSql = 0;
	public $tableName ='';
	public $prepare = 0 ;
	public $preare_start = 1;
	public $preare_end = 2;
	public $sqlSequence = array();
	public function  __construct()	{
		$conn2=mysql_connect($GLOBALS['db_host'].':'.$GLOBALS['db_port'],$GLOBALS['db_user'],$GLOBALS['db_pass'],1,131072) or die("Could not connect: ".mysql_error());
		$db= mysql_select_db($GLOBALS['db_name'],$conn2) or die("Could not select database");
		mysql_query("SET NAMES ".$GLOBALS['db_charset']);
		$this->cache = ClassManager::getCache();
		$this->table = ClassManager::getTableConfig();
	}
	public function preareStart(){
		$this->prepare = $this->preare_start;
	}
	public function preareEnd(){
		foreach ($this->sqlSequence as $v){
			$this->query($v[0], $v[1],true);
		}
	}
	/**
	 * 执行sql语句
	 */
	public  function query($sql,$tableName,$update=FALSE)	{
		if ($update==true){
			if (is_array($tableName)){
				foreach ($tableName as $v){
					$this->cache->delSql($v);
				}
			}
			else{
			//删除缓存
				$this->cache->delSql($tableName);
			}
		}
		if (self::$showSql==1){
			echo $sql.'<br/>';
		}
		$this->query=mysql_query($sql) or exit ('sql语句错误'.$sql);
		return $this->query;
	}
	/**
	 * $where 为素组;
	 */
	public function onedata1($tableName,$column=" * ",$where=""){
		$data= $this->datalist1 ($tableName,$column,$where,1);
		if(!empty($data)){
			return $data[0];
		}
	}

	/**
	 * 获得一条
	 */
	public function onedata($tableName,$column=" * ",$where=""){
		$data= $this->datalist ($tableName,$column,$where,1);
		if(!empty($data)){
			return $data[0];
		}
		return "";
	}

	/**
	 * 获得组数据
	 */
	public function dataPairList($tableName,$column=" * ",$where=""){
		$data= $this->datalist ($tableName,$column,$where);
		return $data;
	}

	/**
	 * 获取一条记录读的是缓存
	 */
	public function oneDataCache($tableName,$key,$v){
		$where = "where `".$key."`='".$v."'";
		if ($this->table->inIndex($tableName, $key)==1){
			$data = $this->cache->indexCacheRead($tableName,$key,$v);
			if ($data==false)		{
				$sql = "select * from ".$this->table->name($tableName)." ".$where." limit 0 , 1";
				$this->query($sql,$tableName);
				$list=array();
				$data = mysql_fetch_array($this->query);
				$this->cache->indexCacheSave($tableName,$key, $v,$data);
			}
		}
		else{
			$data = $this->onedata($tableName,"*",$where);
		}
		return $data;
	}
	
	
	/**
	 * 获取列表  , where 为数组  k 为键 , 0 为值   , 1为运算符 2为 and  or。友情提示不适合  涉及多表关联和，复杂的sql查询
	 */
	public function dataList1($tableName,$column="*",$where="", $order="" ,$limit=0){
		$where_str = "";
		if (is_array($where)){
			$where = $this->table->getFieldValue($tableName, $where);
			$wheretmp = array();
			$i=0;
			foreach ($where as $k=> $v){
				$i++;
				if (ClassManager::getToos()->getmaxdim($v)==2){
					foreach ($v as $v1){
						$i++;
						if (!is_array($v1)){
							$v=array($v1,'=','and');
						}
						if (empty($v1[1])){
							$v1[1] = "=";
						}
						if (empty($v1[2])){
							$v1[2] = "and";
						}
						if ($v1[1]=='in'){
					
							$tmp1 =  " `".$k."` ".$v1[1]."(".$v1[0].")" ;
						}
						else{
							$tmp1 =  " `".$k."` ".$v1[1]."'".$v1[0]."'" ;
						}
						if ($i==2){
							$wheretmp[] = $tmp1;
						}
						else{
							$wheretmp[] = " ".$v1[2]." ".$tmp1;
						}
					}
				}
				else{
					if (!is_array($v)){
						$v=array($v,'=','and');
					}
					if (empty($v[1])){
						$v[1] = "=";
					}
					if (empty($v[2])){
						$v[2] = "and";
					}
					if (trim($v[1])=='in'){
						$tmp =  " `".$k."` ".$v[1]."(".$v[0].")" ;
					}
					else{
						$tmp =  " `".$k."` ".$v[1]."'".$v[0]."'" ;
					}
					if ($i==1){
						$wheretmp[] = $tmp;
					}
					else{
						$wheretmp[] = " ".$v[2]." ".$tmp;
					}
				}
			}
			if (!empty($where)){
				$where_str .= 'where '.implode("",$wheretmp);
			}
		}else{
			$where_str .= $where;
		}

		$where_str = isset($where_str)?$where_str:"";	
		if(stripos(strtolower($order),'by')>0){
			$where_str = $where_str.$order;
		}
		
		return $this->datalist($tableName,$column,$where_str,$limit);
	}
	
	/**
	 * 列表组成sql
	 * @param unknown $tableName  表名
	 * @param unknown $column 列名
	 * @param unknown $where 条件
	 * @param unknown $limit 条数
	 */
	public function datalist ($tableName,$column="*",$where="",$limit=0)	{
		if (is_array($limit))		{
			$limits="limit ".$limit[0]." , ".$limit[1];
		}
		else {
			if ($limit==0)	{
				$limits="";
			}
			else {
				$limits="limit 0 , ".$limit;
			}
		}

		$sql = "select ".$column." from ".$this->table->name($tableName)." ".$where." ".$limits;
		return $this->data($tableName, $sql);
	}
	
	public function dataMutillist ($table_flg,$sql_where="",$limit=0)	{
		if (is_array($limit))		{
			$limits="limit ".$limit[0]." , ".$limit[1];
		}
		else {
			if ($limit==0)	{
				$limits="";
			}
			else {
				$limits="limit 0 , ".$limit;
			}
		}

		$sql = $sql_where.$limits;
		return $this->data($table_flg,$sql);


	}
	
	/**
	 * 更新
	 * @param unknown $tableName
	 * @param unknown $column 更新列名
	 * @param unknown $where 条件
	 */
	public function update($tableName,$column,$where="")	{

		if (!empty($where)){
			$this->updates($tableName, $column,$where);
		}
		else{
			$this->insert($tableName, $column);
		}

	}
	/**
	 * 更新
	 * @param unknown $tableName
	 * @param unknown $column 更新列名
	 * @param unknown $where 条件
	 */
	public function updates($tableName,$column,$where="")	{
		$this->deleteIndexCache($tableName, $where);
		$tmp = array();
		$column = $this->table->getFieldValue($tableName, $column);

		foreach ($column as $k=>$v){
			array_push($tmp, "`".$k."` = '".mysql_real_escape_string($v)."'");
		}
		$tmps = implode(",",$tmp);
		$sql = "update  ".$this->table->name($tableName)." set ".$tmps." ".$where;
		$this->query1($sql, $tableName,true);
	}
	/*
	 * 删除索引缓存
	*/
	public function deleteIndexCache($tableName,$where){
		$list = $this->datalist($tableName,"*",$where);
		foreach ($list as $v){
			foreach ($this->table->table[$tableName]['index'] as $vv){
				$this->cache->deleteIndexCache($tableName, $vv, $v[$vv]);
			}
		}
	}
	/**
	 * 添加
	 * @param unknown $tableName
	 * @param unknown $column
	 */
	public function insert($tableName,$column){
		$tmp = array();
		$tmpv = array ();
		$column = $this->table->getFieldValue($tableName, $column);
		foreach ($column as $k=>$v){
			array_push($tmp, "`".$k."`");
			array_push($tmpv, "'".mysql_real_escape_string($v)."'");
		}
		$sql = "insert  into ".$this->table->name($tableName)." (".implode(",",$tmp).") values (".implode(",", $tmpv).")";
		$this->query1($sql, $tableName);
		return $this->addReturnId();
	}
	public function query1($sql , $tableName){
		if ($this->prepare == $this->preare_start){
			$this->storageSqlSequence($sql, $tableName);
			return ;
		}
		$this->query($sql, $tableName);
	}
	public function storageSqlSequence($sql , $tableName){
		$this->sqlSequence[] = array($sql,$tableName);
	}
	public function insertArray($tableName,$field,$value){
		if (!is_array($field)){
			$field=array($field);
		}
		foreach ($field as $k=>$v){
			$field1[$k]='`'.$v.'`';
		}
		$tmp1= array();
		foreach ($value as $v){
			$tmp = array();
			foreach ($field as $v1){
				array_push($tmp,"'".mysql_real_escape_string($v[$v1])."'");
			}
			array_push($tmp1,'('.implode(',',$tmp).')');
		}

		$sql = "insert  into ".$this->table->name($tableName)." (".implode(",",$field1).") values ".implode(",", $tmp1);
		$this->query1($sql, $tableName);
	}
	public function addReturnId(){
		$this->query('SELECT LAST_INSERT_ID()','');
		$v =  mysql_fetch_array($this->query);
		return $v[0];
	}
	/**
	 * 删除
	 * @param unknown $tableName
	 * @param unknown $where
	 */
	public function delete ($tableName,$where)	{
		$this->deleteIndexCache($tableName, $where);
		$sql = "delete from ".$this->table->name($tableName)." ".$where;
		$this->query1($sql, $tableName);

	}
	/**
	 * 基本数据
	 * @param unknown $tableName
	 * @param unknown $sql
	 */
	public function data($tableName,$sql)	{
		$this->sql = $sql;
		//获得缓存
		$cache = $this->cache->sqlCacheRead($tableName,$sql);
		if ($cache==false)		{
			$this->query($sql,$tableName);
			$list=array();
			while ($v = mysql_fetch_array($this->query)){
				array_push($list, $v);
			}
			//保存缓存
			$this->cache->cacheSave($tableName,$sql,$list);
		}
		else{
			if (self::$showSql==1){
				echo $sql.'<br/>';
			}
			$list =$cache;
		}
		return $list;
	}
}